﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Bing.Base;

namespace Bing.DataAccess
{
    class SqlServer : IDbHelper
    {
        string connstr = "";

        public SqlServer(string conn)
        {
            connstr = conn;
        }

        public void Add(BingSession sessinfo, ref IEntity entity)
        {
            ExecSql(null, GetAddSql(entity));
        }

        public void AddList(BingSession sessinfo, ref List<IEntity> entityList)
        {
            string[] sqllist = new string[entityList.Count];
            for (int i = 0; i < entityList.Count; i++)
            {
                sqllist[i] = GetAddSql(entityList[i]);
            }
            ExecSql(null, sqllist);
        }

        public string GetAddSql(IEntity entity)
        {
            entity.SetValue(entity.GetPrimaryKey(), BingBase.GetId());
            string strSql = "insert into " + entity.GetTableName() + " values( ";
            foreach (string str in entity.GetAllColumn())
            {
                if (entity.GetColumnIsDB(str))
                {
                    object values = entity.GetValue(str);
                    if (str.EndsWith("_ObjType"))
                    {
                        if (values == null || string.IsNullOrEmpty(values.ToString()))
                        {
                            entity.SetValue(str, BingBase.GetSessinfo().Dbsign);
                        }
                    }
                    else if (str.EndsWith("_CreateDate") || str.EndsWith("_EditDate"))
                    {
                        if (values == null || ((DateTime)values) == DateTime.MinValue)
                        {
                            entity.SetValue(str, DateTime.Now);
                        }
                    }
                    else if (str.EndsWith("_Creator") || str.EndsWith("_Editor"))
                    {
                        if (values == null || string.IsNullOrEmpty(values.ToString()))
                        {
                            entity.SetValue(str, BingBase.GetSessinfo().UserId);
                        }
                    }

                    if (values is decimal)
                    {
                        strSql += values.ToString() + ",";
                    }
                    else if (values is DateTime)
                    {
                        if ((DateTime)values == DateTime.MinValue)
                        {
                            strSql += "convert(datetime,'" + DateTime.MaxValue.ToString() + "',120),";
                        }
                        else
                        {
                            strSql += "convert(datetime,'" + values.ToString() + "',120),";
                        }
                    }
                    else
                    {
                        strSql += "'" + values.ToString().Replace("'", "") + "',";
                    }
                }
            }
            return strSql.Trim(',')+")";
        }


        public void Update(BingSession sessinfo, ref IEntity entity)
        {
            ExecSql(null, GetUpdateSql(entity));
        }

        public void UpdateList(BingSession sessinfo, ref List<IEntity> entityList)
        {
            string[] sqllist = new string[entityList.Count];
            for (int i = 0; i < entityList.Count; i++)
            {
                sqllist[i] = GetUpdateSql(entityList[i]);
            }
            ExecSql(null, sqllist);
        }
   
        public string GetUpdateSql(IEntity entity)
        {
            string strSql = "update " + entity.GetTableName() + " set ";
            foreach (string str in entity.GetAllColumn())
            {
                if (entity.GetColumnIsDB(str))
                {
                    if (str != entity.GetPrimaryKey())
                    {
                        object values = entity.GetValue(str);
                        if (values is decimal)
                        {
                            strSql += str + "=" + values.ToString() + ",";
                        }
                        else if (values is DateTime)
                        {
                            if ((DateTime)values == DateTime.MinValue)
                            {
                                strSql += str + "=convert(datetime,'" + DateTime.MaxValue.ToString() + "',120),";
                            }
                            else
                            {
                                strSql += str + "=convert(datetime,'" + values.ToString() + "',120),";
                            }
                        }
                        else
                        {
                            strSql += str + "='" + values.ToString().Replace("'", "") + "',";
                        }
                    }
                }
            }
            strSql =strSql.Trim(',')+" where " + entity.GetPrimaryKey() + "='" + entity.GetValue(entity.GetPrimaryKey()) + "'";
            return strSql;
        }

        public void DelByPrimaryKey(BingSession sessinfo, params IEntity[] entityList)
        {
            string[] sqllist = new string[entityList.Length];
            for (int i = 0; i < entityList.Length; i++)
            {
                IEntity e = entityList[i];
                sqllist[i] = "delete from  " + e.GetTableName() + " where " +
                    e.GetPrimaryKey() + "='" + e.GetValue(e.GetPrimaryKey()).ToString() + "'";
            }
            ExecSql(null, sqllist);
        }

        public void GetByPrimaryKey(ref IEntity entity)
        {
            string sql = "select * from " + entity.GetTableName() + " where " + 
                entity.GetPrimaryKey() + "='" + entity.GetValue(entity.GetPrimaryKey()).ToString() + "'";
            DataSet ds = ExecSqlQuery(null, sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                entity.FromDataRow(ds.Tables[0].Rows[0]);
            }
        }

        public void GetByPrimaryKeyList(ref List<IEntity> entityList)
        {
            string[] sqllist = new string[entityList.Count];
            for(int i=0;i<entityList.Count;i++)
            {
                IEntity e = entityList[i];
                sqllist[i] = "select * from "+e.GetTableName()+" where "+
                    e.GetPrimaryKey()+"='"+e.GetValue(e.GetPrimaryKey()).ToString()+"'";
            }
            DataSet ds = ExecSqlQuery(null,sqllist);
            for (int i = 0; i < entityList.Count; i++)
            {
                IEntity e = entityList[i];
                e.FromDataRow(ds.Tables[i].Rows[0]);
            }
        }

        public System.Data.DataSet GetList(string strWhere, IEntity entity)
        {
            StringBuilder strSql=new StringBuilder();
			strSql.Append("select * ");
			strSql.Append(" FROM "+entity.GetTableName());
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			return ExecSqlQuery(null,strSql.ToString());
        }

        public System.Data.DataSet GetListTop(int Top, string strWhere, string filedOrder, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * FROM "+entity.GetTableName());
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            if (!string.IsNullOrEmpty(filedOrder))
            {
                strSql.Append("order by " + filedOrder);
            }
            else
            {
                strSql.Append("order by " + entity.GetPrimaryKey() + " desc");
            }
            return ExecSqlQuery(null,strSql.ToString());
        }

        public int GetRecordCount(string strWhere, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM "+entity.GetTableName());
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet obj = ExecSqlQuery(null,strSql.ToString());
            return Convert.ToInt32(obj.Tables[0].Rows[0][0]);
        }

        public System.Data.DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, IEntity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T."+entity.GetPrimaryKey()+" desc");
            }
            strSql.Append(")AS Row, T.*  from "+entity.GetTableName()+" T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return ExecSqlQuery(null,strSql.ToString());
        }

        public System.Data.DataSet ExecSqlQuery(BingSession sessinfo, params string[] strSql)
        {
            using (SqlConnection connection = new SqlConnection(connstr))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    for (int n = 0; n < strSql.Length; n++)
                    {
                        string TableName = "Table" + (ds.Tables.Count == 0 ? "" : ds.Tables.Count.ToString());
                        DataTable dt = new DataTable(TableName);
                        SqlDataAdapter command = new SqlDataAdapter(strSql[n], connection);
                        command.Fill(dt);
                        ds.Tables.Add(dt);
                    }
                }
                catch (Exception ex)
                {
                    BingBase.WriteErrorLog("ExecSqlQuery", ex);
                }
                return ds;
            }
        }

        public List<int> ExecSql(BingSession sessinfo, params string[] strSql)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    List<int> ResCount = new List<int>();
                    for (int n = 0; n < strSql.Length; n++)
                    {
                        string strsql = strSql[n];
                        if (strsql!=null && strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            ResCount.Add(cmd.ExecuteNonQuery());
                        }
                    }
                    tx.Commit();
                    return ResCount;
                }
                catch(Exception ex)
                {
                    tx.Rollback();
                    BingBase.WriteErrorLog("ExecSql", ex);
                }
            }
            return null;
        }

        public System.Data.DataSet ExecProc(BingSession sess, string procName, params BingDbParams[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connstr))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                SqlCommand command = new SqlCommand(procName, connection);
                command.CommandType = CommandType.StoredProcedure;
                if (parameters != null)
                {
                    foreach (BingDbParams parameter in parameters)
                    {
                        if (parameter != null)
                        {
                            switch(parameter._simpletype)
                            {
                                case BingDbParamsType.String_In:
                                    break;
                            }
                            SqlParameter sqlParam = new SqlParameter("@sh_u_Objid", SqlDbType.UniqueIdentifier, 16);
                            command.Parameters.Add(sqlParam);
                        }
                    }
                }
                sqlDA.SelectCommand = command;
                sqlDA.Fill(dataSet);
                connection.Close();
                return dataSet;
            }
        }

        public BingDbType GetDBType()
        {
            return BingDbType.SqlServer;
        }

        public void AddOrEditByte(IEntity entity, string colName, object obj)
        {
            //using (SqlConnection con = new SqlConnection("data source=.;database=test;uid=sa;pwd=123"))
            //{
            //    FileUpload1.SaveAs(Server.MapPath("~/f/") + FileUpload1.FileName);
            //    byte[] buffer = new byte[FileUpload1.PostedFile.ContentLength];
            //    Stream s = FileUpload1.PostedFile.InputStream;
            //    s.Read(buffer, 0, buffer.Length);
            //    SqlCommand cmd = new SqlCommand("insert into t(msg,name) values(@msg,@name)", con);
            //    cmd.Parameters.Add("@msg", SqlDbType.VarBinary).Value = buffer;
            //    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = FileUpload1.FileName;
            //    con.Open();
            //    cmd.CommandTimeout = 40;
            //    cmd.ExecuteNonQuery();
            //    con.Close();
            //}
        }

        public object GetByte(IEntity entity, string colName)
        {
            //using (SqlConnection con = new SqlConnection("data source=.;database=test;uid=sa;pwd=123"))
            //{
            //    SqlCommand cmd = new SqlCommand("select * from t where id=" + TextBox1.Text, con);
            //    con.Open();
            //    cmd.CommandTimeout = 40;
            //    SqlDataReader dr = cmd.ExecuteReader();
            //    while (dr.Read())
            //    {
            //        byte[] bufer = (byte[])dr["msg"];
            //        string path = Server.MapPath("~/o/") + dr["name"].ToString();
            //        if (File.Exists(path))
            //        {
            //            File.Delete(path);
            //        }
            //        FileStream fs = new FileStream(path, FileMode.Create);
            //        fs.Write(bufer, 0, bufer.Length);

            //        fs.Close();
            //    }
            //    dr.Close();
            //    con.Close();
            //}
            return null;
        }

        public void AddOrEditFile(IEntity entity, string colName, string fileName)
        {
            throw new NotImplementedException();
        }

        public string GetFile(IEntity entity, string colName)
        {
            throw new NotImplementedException();
        }


        #region IDbHelper 成员

        public void AddOrEditByte(BingSession sess, string tableName, string colName, object obj)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void AddOrEditFile(BingSession sess, string tableName, string colName, string fileName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void AddOrUpdate(BingSession sessinfo, ref IEntity entity)
        {
            if (string.IsNullOrEmpty(entity.ObjId))
            {
                Add(sessinfo, ref entity);
            }
            else
            {
                Update(sessinfo, ref entity);
            }
        }

        public void AddOrUpdateList(BingSession sessinfo, ref List<IEntity> entityList)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void DelByPrimaryKey(BingSession sessinfo, params string[] primarykeys)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public string GetAddSql(ref IEntity entity)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public IEntity GetByPrimaryKey(string primarykeys)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public List<IEntity> GetByPrimaryKeyList(params string[] primarykeys)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public object GetByte(string tableName, string colName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public string GetFile(string tableName, string colName)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public DataSet GetList(string strWhere, string filedOrder, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, string table)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            strSql.Append(")AS Row, T.*  from " + table + " T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return ExecSqlQuery(null, strSql.ToString());
        }

        public DataSet GetListTop(int Top, string strWhere, string filedOrder, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public int GetRecordCount(string strWhere, string table)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public void Update(BingSession sessinfo, params IEntity[] entityList)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        #endregion
    }
}
